
from openpyxl import Workbook
from openpyxl import load_workbook
from work.bspregion import bspregion

# 到此我的想要的 基本可以解决了
# 资源文件
source_file_path = r'D:\Desktop\exceltest\部门表待处理(新).xlsx'
# 模板文件
model_file_path = r'D:\Desktop\exceltest\导入模板文件.xlsx'
target_path = "D:\\Desktop\\exceltest\\部门\\"
source_sheet_name = '3级区县部门'
source_wb = load_workbook(source_file_path)


source_ws = source_wb[source_sheet_name]
source_set = set()

source_row_num = 2
source_col_num = 19
source_cell = source_ws.cell(source_row_num, source_col_num)
while source_cell.value is not None:
    val = source_cell.value
    source_set.add(val[0: 6])
    source_row_num += 1
    source_cell = source_ws.cell(source_row_num, source_col_num)
print(f'sheet{source_ws.title}的有效行数为:{source_row_num-1}')

for region in source_set:
    region_name = None
    for ob in bspregion:
        if region == ob.get('SHORT_CODE'):
            region_name = ob.get('NAME')
            break
    if region_name is not None:
        target_wb = load_workbook(model_file_path)
        target_ws = target_wb.active
        target_ws.title = '批量导入机构模板'
        target_row_num = 3
        for i in range(2, source_row_num):
            if region == source_ws.cell(i, source_col_num).value[0:6]:
                target_ws[f'A{target_row_num}'].value = source_ws[f'J{i}'].value
                target_ws[f'B{target_row_num}'].value = source_ws[f'J{i}'].value
                target_ws[f'C{target_row_num}'].value = source_ws[f'J{i}'].value[8:18]
                target_ws[f'D{target_row_num}'].value = source_ws[f'C{i}'].value
                target_ws[f'E{target_row_num}'].value = source_ws[f'D{i}'].value
                target_ws[f'F{target_row_num}'].value = '部门'
                target_ws[f'K{target_row_num}'].value = '0'
                target_ws[f'L{target_row_num}'].value = '0'
                target_ws[f'M{target_row_num}'].value = '0'
                target_ws[f'N{target_row_num}'].value = '0'
                target_ws[f'O{target_row_num}'].value = '0'
                target_ws[f'T{target_row_num}'].value = '1'
                target_row_num += 1
        target_wb.save(f'{target_path}{region_name}--{region}.xlsx')

print('完成')
